
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 03/26/2011 12:57:52
-- Generated from EDMX file: D:\Personal\Personal\docs\Master_UPM\2010_MUSS\Tesis\Proyectos_Codigo\ScrumTime\Source\ScrumTime\Models\ScrumTime.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [ScrumTime];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_Releases_Products]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Releases] DROP CONSTRAINT [FK_Releases_Products];
GO
IF OBJECT_ID(N'[dbo].[FK_ScrumDetails_Scrums]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ScrumDetails] DROP CONSTRAINT [FK_ScrumDetails_Scrums];
GO
IF OBJECT_ID(N'[dbo].[FK_ScrumDetails_Tasks]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ScrumDetails] DROP CONSTRAINT [FK_ScrumDetails_Tasks];
GO
IF OBJECT_ID(N'[dbo].[FK_Scrums_Products]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Scrums] DROP CONSTRAINT [FK_Scrums_Products];
GO
IF OBJECT_ID(N'[dbo].[FK_Scrums_Sprints]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Scrums] DROP CONSTRAINT [FK_Scrums_Sprints];
GO
IF OBJECT_ID(N'[dbo].[FK_Sprints_Products]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Sprints] DROP CONSTRAINT [FK_Sprints_Products];
GO
IF OBJECT_ID(N'[dbo].[FK_Stories_Products]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Stories] DROP CONSTRAINT [FK_Stories_Products];
GO
IF OBJECT_ID(N'[dbo].[FK_Stories_Sprints]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Stories] DROP CONSTRAINT [FK_Stories_Sprints];
GO
IF OBJECT_ID(N'[dbo].[FK_Stories_UsabilityFeatures_Stories]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Stories_UsabilityFeatures] DROP CONSTRAINT [FK_Stories_UsabilityFeatures_Stories];
GO
IF OBJECT_ID(N'[dbo].[FK_Stories_UsabilityFeatures_UsabilityFeatures]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Stories_UsabilityFeatures] DROP CONSTRAINT [FK_Stories_UsabilityFeatures_UsabilityFeatures];
GO
IF OBJECT_ID(N'[dbo].[FK_Tasks_Stories]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Tasks] DROP CONSTRAINT [FK_Tasks_Stories];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Products]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Products];
GO
IF OBJECT_ID(N'[dbo].[Releases]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Releases];
GO
IF OBJECT_ID(N'[dbo].[ScrumDetails]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ScrumDetails];
GO
IF OBJECT_ID(N'[dbo].[Scrums]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Scrums];
GO
IF OBJECT_ID(N'[dbo].[Sprints]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Sprints];
GO
IF OBJECT_ID(N'[dbo].[Stories]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Stories];
GO
IF OBJECT_ID(N'[dbo].[Stories_UsabilityFeatures]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Stories_UsabilityFeatures];
GO
IF OBJECT_ID(N'[dbo].[Tasks]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Tasks];
GO
IF OBJECT_ID(N'[dbo].[UsabilityFeatures]', 'U') IS NOT NULL
    DROP TABLE [dbo].[UsabilityFeatures];
GO
IF OBJECT_ID(N'[dbo].[Users]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Users];
GO
IF OBJECT_ID(N'[dbo].[UserSettings]', 'U') IS NOT NULL
    DROP TABLE [dbo].[UserSettings];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Products'
CREATE TABLE [dbo].[Products] (
    [ProductId] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(256)  NOT NULL,
    [Description] nvarchar(1024)  NULL
);
GO

-- Creating table 'Releases'
CREATE TABLE [dbo].[Releases] (
    [ReleaseId] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(50)  NOT NULL,
    [Description] nvarchar(1024)  NULL,
    [Target] datetime  NOT NULL,
    [ProductId] int  NOT NULL
);
GO

-- Creating table 'Sprints'
CREATE TABLE [dbo].[Sprints] (
    [SprintId] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(128)  NOT NULL,
    [Description] nvarchar(1024)  NULL,
    [StartDate] datetime  NOT NULL,
    [FinishDate] datetime  NOT NULL,
    [ProductId] int  NOT NULL
);
GO

-- Creating table 'Stories'
CREATE TABLE [dbo].[Stories] (
    [StoryId] int IDENTITY(1,1) NOT NULL,
    [Narrative] nvarchar(1024)  NOT NULL,
    [Points] int  NOT NULL,
    [UserDefinedId] nvarchar(250)  NULL,
    [Priority] int  NOT NULL,
    [ProductId] int  NOT NULL,
    [SprintId] int  NULL
);
GO

-- Creating table 'Tasks'
CREATE TABLE [dbo].[Tasks] (
    [TaskId] int IDENTITY(1,1) NOT NULL,
    [Description] nvarchar(1024)  NOT NULL,
    [Hours] decimal(18,1)  NULL,
    [StoryId] int  NOT NULL,
    [AssignedTo] nvarchar(250)  NULL
);
GO

-- Creating table 'ScrumDetails'
CREATE TABLE [dbo].[ScrumDetails] (
    [ScrumDetailId] int IDENTITY(1,1) NOT NULL,
    [StoryTaskDescription] nvarchar(1024)  NOT NULL,
    [AssignedTo] nvarchar(256)  NOT NULL,
    [HoursCompleted] decimal(18,1)  NOT NULL,
    [HoursRemaining] decimal(18,1)  NOT NULL,
    [ScrumId] int  NOT NULL,
    [TaskId] int  NOT NULL
);
GO

-- Creating table 'Scrums'
CREATE TABLE [dbo].[Scrums] (
    [ScrumId] int IDENTITY(1,1) NOT NULL,
    [ProductId] int  NOT NULL,
    [SprintId] int  NOT NULL,
    [DateOfScrum] datetime  NOT NULL
);
GO

-- Creating table 'UserSettings'
CREATE TABLE [dbo].[UserSettings] (
    [UserSettingId] int IDENTITY(1,1) NOT NULL,
    [Username] nvarchar(128)  NOT NULL,
    [CurrentProduct] int  NULL,
    [CurrentSprint] int  NULL,
    [LastMainTabSelected] int  NULL
);
GO

-- Creating table 'UsabilityFeatures'
CREATE TABLE [dbo].[UsabilityFeatures] (
    [UsabilityFeatureId] int IDENTITY(1,1) NOT NULL,
    [Description] nvarchar(1024)  NULL,
    [Name] nvarchar(100)  NULL
);
GO

-- Creating table 'Users'
CREATE TABLE [dbo].[Users] (
    [UserId] uniqueidentifier  NOT NULL
);
GO

-- Creating table 'Stories_UsabilityFeatures'
CREATE TABLE [dbo].[Stories_UsabilityFeatures] (
    [Stories_StoryId] int  NOT NULL,
    [UsabilityFeatures_UsabilityFeatureId] int  NOT NULL
);
GO

-- Creating table 'UserProduct'
CREATE TABLE [dbo].[UserProduct] (
    [Users_UserId] uniqueidentifier  NOT NULL,
    [Products_ProductId] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [ProductId] in table 'Products'
ALTER TABLE [dbo].[Products]
ADD CONSTRAINT [PK_Products]
    PRIMARY KEY CLUSTERED ([ProductId] ASC);
GO

-- Creating primary key on [ReleaseId] in table 'Releases'
ALTER TABLE [dbo].[Releases]
ADD CONSTRAINT [PK_Releases]
    PRIMARY KEY CLUSTERED ([ReleaseId] ASC);
GO

-- Creating primary key on [SprintId] in table 'Sprints'
ALTER TABLE [dbo].[Sprints]
ADD CONSTRAINT [PK_Sprints]
    PRIMARY KEY CLUSTERED ([SprintId] ASC);
GO

-- Creating primary key on [StoryId] in table 'Stories'
ALTER TABLE [dbo].[Stories]
ADD CONSTRAINT [PK_Stories]
    PRIMARY KEY CLUSTERED ([StoryId] ASC);
GO

-- Creating primary key on [TaskId] in table 'Tasks'
ALTER TABLE [dbo].[Tasks]
ADD CONSTRAINT [PK_Tasks]
    PRIMARY KEY CLUSTERED ([TaskId] ASC);
GO

-- Creating primary key on [ScrumDetailId] in table 'ScrumDetails'
ALTER TABLE [dbo].[ScrumDetails]
ADD CONSTRAINT [PK_ScrumDetails]
    PRIMARY KEY CLUSTERED ([ScrumDetailId] ASC);
GO

-- Creating primary key on [ScrumId] in table 'Scrums'
ALTER TABLE [dbo].[Scrums]
ADD CONSTRAINT [PK_Scrums]
    PRIMARY KEY CLUSTERED ([ScrumId] ASC);
GO

-- Creating primary key on [UserSettingId] in table 'UserSettings'
ALTER TABLE [dbo].[UserSettings]
ADD CONSTRAINT [PK_UserSettings]
    PRIMARY KEY CLUSTERED ([UserSettingId] ASC);
GO

-- Creating primary key on [UsabilityFeatureId] in table 'UsabilityFeatures'
ALTER TABLE [dbo].[UsabilityFeatures]
ADD CONSTRAINT [PK_UsabilityFeatures]
    PRIMARY KEY CLUSTERED ([UsabilityFeatureId] ASC);
GO

-- Creating primary key on [UserId] in table 'Users'
ALTER TABLE [dbo].[Users]
ADD CONSTRAINT [PK_Users]
    PRIMARY KEY CLUSTERED ([UserId] ASC);
GO

-- Creating primary key on [Stories_StoryId], [UsabilityFeatures_UsabilityFeatureId] in table 'Stories_UsabilityFeatures'
ALTER TABLE [dbo].[Stories_UsabilityFeatures]
ADD CONSTRAINT [PK_Stories_UsabilityFeatures]
    PRIMARY KEY NONCLUSTERED ([Stories_StoryId], [UsabilityFeatures_UsabilityFeatureId] ASC);
GO

-- Creating primary key on [Users_UserId], [Products_ProductId] in table 'UserProduct'
ALTER TABLE [dbo].[UserProduct]
ADD CONSTRAINT [PK_UserProduct]
    PRIMARY KEY NONCLUSTERED ([Users_UserId], [Products_ProductId] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [ProductId] in table 'Releases'
ALTER TABLE [dbo].[Releases]
ADD CONSTRAINT [FK_Releases_Products]
    FOREIGN KEY ([ProductId])
    REFERENCES [dbo].[Products]
        ([ProductId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Releases_Products'
CREATE INDEX [IX_FK_Releases_Products]
ON [dbo].[Releases]
    ([ProductId]);
GO

-- Creating foreign key on [ProductId] in table 'Sprints'
ALTER TABLE [dbo].[Sprints]
ADD CONSTRAINT [FK_Sprints_Products]
    FOREIGN KEY ([ProductId])
    REFERENCES [dbo].[Products]
        ([ProductId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Sprints_Products'
CREATE INDEX [IX_FK_Sprints_Products]
ON [dbo].[Sprints]
    ([ProductId]);
GO

-- Creating foreign key on [ProductId] in table 'Stories'
ALTER TABLE [dbo].[Stories]
ADD CONSTRAINT [FK_Stories_Products]
    FOREIGN KEY ([ProductId])
    REFERENCES [dbo].[Products]
        ([ProductId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Stories_Products'
CREATE INDEX [IX_FK_Stories_Products]
ON [dbo].[Stories]
    ([ProductId]);
GO

-- Creating foreign key on [SprintId] in table 'Stories'
ALTER TABLE [dbo].[Stories]
ADD CONSTRAINT [FK_Stories_Sprints]
    FOREIGN KEY ([SprintId])
    REFERENCES [dbo].[Sprints]
        ([SprintId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Stories_Sprints'
CREATE INDEX [IX_FK_Stories_Sprints]
ON [dbo].[Stories]
    ([SprintId]);
GO

-- Creating foreign key on [StoryId] in table 'Tasks'
ALTER TABLE [dbo].[Tasks]
ADD CONSTRAINT [FK_Tasks_Stories]
    FOREIGN KEY ([StoryId])
    REFERENCES [dbo].[Stories]
        ([StoryId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Tasks_Stories'
CREATE INDEX [IX_FK_Tasks_Stories]
ON [dbo].[Tasks]
    ([StoryId]);
GO

-- Creating foreign key on [ProductId] in table 'Scrums'
ALTER TABLE [dbo].[Scrums]
ADD CONSTRAINT [FK_Scrums_Products]
    FOREIGN KEY ([ProductId])
    REFERENCES [dbo].[Products]
        ([ProductId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Scrums_Products'
CREATE INDEX [IX_FK_Scrums_Products]
ON [dbo].[Scrums]
    ([ProductId]);
GO

-- Creating foreign key on [ScrumId] in table 'ScrumDetails'
ALTER TABLE [dbo].[ScrumDetails]
ADD CONSTRAINT [FK_ScrumDetails_Scrums]
    FOREIGN KEY ([ScrumId])
    REFERENCES [dbo].[Scrums]
        ([ScrumId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ScrumDetails_Scrums'
CREATE INDEX [IX_FK_ScrumDetails_Scrums]
ON [dbo].[ScrumDetails]
    ([ScrumId]);
GO

-- Creating foreign key on [TaskId] in table 'ScrumDetails'
ALTER TABLE [dbo].[ScrumDetails]
ADD CONSTRAINT [FK_ScrumDetails_Tasks]
    FOREIGN KEY ([TaskId])
    REFERENCES [dbo].[Tasks]
        ([TaskId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ScrumDetails_Tasks'
CREATE INDEX [IX_FK_ScrumDetails_Tasks]
ON [dbo].[ScrumDetails]
    ([TaskId]);
GO

-- Creating foreign key on [SprintId] in table 'Scrums'
ALTER TABLE [dbo].[Scrums]
ADD CONSTRAINT [FK_Scrums_Sprints]
    FOREIGN KEY ([SprintId])
    REFERENCES [dbo].[Sprints]
        ([SprintId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Scrums_Sprints'
CREATE INDEX [IX_FK_Scrums_Sprints]
ON [dbo].[Scrums]
    ([SprintId]);
GO

-- Creating foreign key on [Stories_StoryId] in table 'Stories_UsabilityFeatures'
ALTER TABLE [dbo].[Stories_UsabilityFeatures]
ADD CONSTRAINT [FK_Stories_UsabilityFeatures_Story]
    FOREIGN KEY ([Stories_StoryId])
    REFERENCES [dbo].[Stories]
        ([StoryId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [UsabilityFeatures_UsabilityFeatureId] in table 'Stories_UsabilityFeatures'
ALTER TABLE [dbo].[Stories_UsabilityFeatures]
ADD CONSTRAINT [FK_Stories_UsabilityFeatures_UsabilityFeature]
    FOREIGN KEY ([UsabilityFeatures_UsabilityFeatureId])
    REFERENCES [dbo].[UsabilityFeatures]
        ([UsabilityFeatureId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Stories_UsabilityFeatures_UsabilityFeature'
CREATE INDEX [IX_FK_Stories_UsabilityFeatures_UsabilityFeature]
ON [dbo].[Stories_UsabilityFeatures]
    ([UsabilityFeatures_UsabilityFeatureId]);
GO

-- Creating foreign key on [Users_UserId] in table 'UserProduct'
ALTER TABLE [dbo].[UserProduct]
ADD CONSTRAINT [FK_UserProduct_User]
    FOREIGN KEY ([Users_UserId])
    REFERENCES [dbo].[Users]
        ([UserId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Products_ProductId] in table 'UserProduct'
ALTER TABLE [dbo].[UserProduct]
ADD CONSTRAINT [FK_UserProduct_Product]
    FOREIGN KEY ([Products_ProductId])
    REFERENCES [dbo].[Products]
        ([ProductId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserProduct_Product'
CREATE INDEX [IX_FK_UserProduct_Product]
ON [dbo].[UserProduct]
    ([Products_ProductId]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------